package com.mysharding.shardingUtils;

import org.apache.shardingsphere.infra.hint.HintManager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;

@Component
public class DBUtil {
    @Autowired
    @Lazy
    private DataSource dataSource;
    private final static String sys_user = "sys_user";


    public String createUploadiotrundataTable(String tablename) {
        String creatsql = String.format("CREATE TABLE `%s`  (\n" +
                "  `user_id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '用户ID',\n" +
                "  `dept_id` bigint(0) NULL DEFAULT NULL COMMENT '部门ID',\n" +
                "  `user_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户账号',\n" +
                "  `nick_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户昵称',\n" +
                "  `user_type` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '00' COMMENT '用户类型（00系统用户）',\n" +
                "  `email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '用户邮箱',\n" +
                "  `phonenumber` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '手机号码',\n" +
                "  `sex` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '用户性别（0男 1女 2未知）',\n" +
                "  `avatar` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '头像地址',\n" +
                "  `password` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '密码',\n" +
                "  `status` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '帐号状态（0正常 1停用）',\n" +
                "  `del_flag` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',\n" +
                "  `login_ip` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '最后登陆IP',\n" +
                "  `login_date` datetime(0) NULL DEFAULT NULL COMMENT '最后登陆时间',\n" +
                "  `create_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '创建者',\n" +
                "  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',\n" +
                "  `update_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '更新者',\n" +
                "  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',\n" +
                "  `remark` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',\n" +
                "  PRIMARY KEY (`user_id`) USING BTREE\n" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户信息表' ROW_FORMAT = Dynamic;", tablename);
        try (
                Connection conn = dataSource.getConnection();
                Statement stmt = conn.createStatement();
//                HintManager hintManager = HintManager.getInstance();
        ) {
//            hintManager.setMasterRouteOnly();
            //执行创建表
            System.out.println("//创建表");
            if (0 == stmt.executeUpdate(creatsql)) {
                System.out.println("成功创建表！");
            } else {
                System.out.println("创建表失败！");
            }
            stmt.close();
            conn.close();
            System.out.println("//关闭资源");
            return tablename;
        } catch (Exception e) {
            e.getStackTrace();
        }
        return null;
    }


    public List<String> getAllTable(String tablename) {
        List<String> list = Collections.synchronizedList(new LinkedList<>());
        String sql = "select * from "+tablename+" limit 1";
        try (
                Connection conn = dataSource.getConnection();
                PreparedStatement preparedStatement = conn.prepareStatement(sql);
        ) {
            try (ResultSet rs = preparedStatement.executeQuery()) {

            } catch (Exception e) {
                list.add(tablename);
            }
        } catch (Exception e) {
            e.getStackTrace();
        }
        return list;
    }
}
